CLCK 13: Подключение справочников на примере geoip (maxmind)

Описание:

В данном задании мы рассмотрим что такое справочники в ClickHouse, какие они бывают, в чем их преимущество и как их подключить.

Начнем мы с простого, а именно, что такое справочник или словарь? Это хранение данный в виде ключ - значение. Представьте, что у вас есть таблица с данными вида - date, event_type, user_id. Здесь мы храним дату события, тип события и айдишник пользователя. В финальном отчете мы естественно хотим видеть не внутренний идентификатор пользователя, а его email или имя. Для этого и используются словари - вы можете по айдишнику пользователя получить его имя. При этом заметьте что если вы храните в сырых данных числовые идентификаторы - то выборка происходит быстрее. А уже на финальном этапе вы можете сматчить идентификатор с каким-то строковым значением.

У Кликхауса есть различные специальные функции, которые можно использовать при работе со словарями. Данный метод получается гораздо проще и эффективнее по сравнению с использованием JOIN в таблицах-справочниках, когда матчинг хранится локально в соседних таблицах.

За счет чего работать со словарями в ClickHouse эффективней?

• ClickHouse полностью или частично хранит словари в оперативной памяти в виде плоского массива данных.
• Периодически обновляет словари и динамически подгружает отсутствующие значения.
• Позволяет создавать внешние словари с помощью xml-файлов или DDL-запросов (о чем я расскажу дальше).

Типы словарей

В ClickHouse есть встроенные словари, которые вы можете свободно использовать при работе. Но если их не хватает, вы легко можете подключить любые внешние словари. Именно это мы и будем делать.

Подключить внешний словарь можно многими способами. Будь это локальный текстовый/исполняемый файл, HTTP(S) ресурс или другая СУБД.

Сконфигурировать подключение можно в конфигурационном файле config.xml. За это отвечает параметр dictionaries_config. Также с помощью параметра dictionaries_lazy_load можно указать, будут словари загружаться при старте сервера или непосредственно при их использовании. Еще стоит заметить, что обновление словарей не блокирует обработку запросов. В это время запросы просто используют старую версию словарей.

Для просмотра информации о словарях, сконфигурированных на сервере, можно использовать системную таблицу system.dictionaries, в ней можно найти:

• статус словаря;
• конфигурационные параметры;
• метрики, например, количество занятой словарем RAM или количество запросов к нему с момента загрузки.

Конфигурация словарей

Словари можно конфигурировать через xml-файлы или DDL-запросы. Можете использовать любой удобный вариант, но в примере мы применим DDL-запросы, поскольку это — самый простой способ создавать и контролировать словари.

Пример шаблона для создания словаря через DDL-запрос:

CREATE DICTIONARY dict_name
(
 attribute_name UInt64 DEFAULT 0
 attribute_name String DEFAULT 'someString'
)
PRIMARY KEY ...
SOURCE(...)
LAYOUT(...)
LIFETIME(...)

Поля настройки:

• dict_name — имя словаря.
• attribute_name — атрибуты словаря.
• primary key — первичный ключ словаря.
• source — источник словаря. Подробнее рассмотрим это далее.
• layout — размещение словаря в памяти. От этого значения зависит скорость обработки словаря.
• lifetime — периодичность обновления словарей в секундах.

Источники внешних словарей

Внешние словари можно подключить через множество разных источников. Основные из них — это:

• локальный файл;
• исполняемый файл;
• HTTP(s);
• СУБД.

Самые распространенные способы подключения — через локальный файл либо СУБД, поэтому именно эти примеры мы и рассмотрим далее.

Локальный файл

Пример подключения словаря DDL-запросом через локальный файл имеет следующий вид:

SOURCE(FILE(path '/opt/dictionaries/clients.csv' format 'CSV'))
SETTINGS(format_csv_allow_single_quotes = 0)

• path — отвечает за путь к файлу со словарем;
• format — отвечает за формат этого файла;
• settings — дополнительные параметры (необязательное поле).

СУБД

Рассмотрим подключение СУБД на примере MySQL базы данных.

Пример настройки:

SOURCE(MYSQL(
 port 3306
 user 'clickhouse'
 password 'secret_password'
 replica(host 'example01-1' priority 1)
 replica(host 'example01-2' priority 1)
 db 'db_name'
 table 'table_name'
 where 'id=10'
 invalidate_query 'SQL_QUERY'
))

• port — порт сервера MySQL. Можно задать отдельно для каждой реплики внутри тега <replica>.
• user — имя пользователя MySQL. Можно задать отдельно для каждой реплики внутри тега <replica>.
• password — пароль пользователя MySQL. Можно задать отдельно для каждой реплики внутри тега <replica>.
• replica — блок конфигурации реплики. Блоков может быть несколько.
• db — имя базы данных.
• table — имя таблицы.
• where — условие выбора. Синтаксис полностью совпадает с синтаксисом секции WHERE в MySQL, к примеру, id >= 3 AND id < 10 (необязательный параметр).
• invalidate_query — запрос для проверки статуса словаря (необязательный параметр).

Хранение словарей в памяти

Существует много способов хранения словарей в памяти ClickHouse:

• flat
• hashed
• sparse_hashed
• cache
• direct
• range_hashed
• complex_key_hashed
• complex_key_cache
• complex_key_direct
• ip_trie

Но сам ClickHouse рекомендует использовать только три из них, поскольку в этом случае скорость обработки словарей максимальна, — это flat, hashed и complex_key_hashed. Примеры этих способов хранения мы и рассмотрим.

Flat

В этом способе словари полностью хранятся в оперативной памяти в виде плоских массивов, при этом объем занятой памяти пропорционален размеру самого большого по размеру ключа словаря. Ключ словаря должен иметь тип UInt64 и не должен быть длиннее 500 000, иначе ClickHouse бросит исключение и не создаст словарь.

Этот метод обеспечивает максимальную производительность из доступных способов хранения.

Пример конфигурации:

LAYOUT(FLAT())

Hashed

Словарь полностью хранится в оперативной памяти в виде хеш-таблиц и может содержать любое количество элементов с произвольными идентификаторами. На практике количество ключей может достигать десятков миллионов элементов.

LAYOUT(HASHED())

Сomplex_key_hashed

Тип размещения предназначен для использования с составными ключами. Аналогичен hashed способу.

Пример конфигурации:

LAYOUT(COMPLEX_KEY_HASHED())

Ключи

ClickHouse имеет поддержку следующих видов ключей:

• Числовой ключ. UInt64. Описывается в теге <id> или ключевым словом PRIMARY KEY.
• Составной ключ. Набор значений разного типа. Описывается в теге <key> или ключевым словом PRIMARY KEY.

Числовой ключ

Тип: UInt64.

CREATE DICTIONARY (
 user_id UInt64,
 ...
)
PRIMARY KEY user_id
...

• PRIMARY KEY – имя столбца с ключами.

Составной ключ

Ключом может быть кортеж (tuple) из полей произвольных типов. В этом случае layout должен быть complex_key_hashed или complex_key_cache.

Структура ключа задается в элементе <key>. Поля ключа задаются в том же формате, что и атрибуты словаря. Пример:

CREATE DICTIONARY ( field1 String, field2 String ... )
PRIMARY KEY field1, field2
...

Пример использования внешних словарей в ClickHouse

Один из популярных кейсов использования словарей в ClickHouse — это агрегация данных по странам на основе IP (v4) адресов.

Представим, что перед нами задача: из данных колонки с ip String получить в запросе колонку с country String. Для ее решения возьмем довольно популярные базы GeoIP2 от MaxMind.

MaxMind предоставляет со своими .mmdb базами API для большинства популярных языков программирования.

В ClickHouse нет возможности загрузить в словарь формат .mmdb, но нам это и не понадобится – MaxMind позволяет загрузить свои базы в виде нескольких CSV, чем мы и воспользуемся.

Для того чтобы связать IP со страной, необходимо скачать следующие файлы:

• GeoIP2-Country-Blocks-IPv4.csv – здесь содержатся связи IP префиксов и ID стран;
• GeoIP2-Country-Locations-en.csv – а здесь уже названия стран на английском.

Далее заведем соответствующие словари с помощью DDL:

CREATE DICTIONARY dicts.geoip_country_blocks_ipv4 (
 network String DEFAULT '',
 geoname_id UInt64 DEFAULT 0,
 registered_country_geoname_id UInt64 DEFAULT 0,
 represented_country_geoname_id UInt64 DEFAULT 0,
 is_anonymous_proxy UInt8 DEFAULT 0,
 is_satellite_provider UInt8 DEFAULT 0
)
PRIMARY KEY network
SOURCE(FILE(
 path '/var/lib/clickhouse/user_files/GeoIP2-Country-Blocks-IPv4.csv'
 format 'CSVWithNames'
))
LAYOUT(IP_TRIE())
LIFETIME(300);

В словаре geoip_country_blocks_ipv4 мы должны указать два основных атрибута:

• network – IP префикс сети, он же и будет ключом словаря.
• geoname_id – ID страны.

Остальные атрибуты – в соответствии с заголовком в CSV.

Чтобы ClickHouse мог корректно сопоставить префикс сети и ID, нам необходимо использовать тип размещения ip_trie. Для получения значений из такого словаря необходимо будет передавать IP-адрес в числовом представлении.

Теперь geoip_country_locations_en:

CREATE DICTIONARY dicts.geoip_country_locations_en (
 geoname_id UInt64 DEFAULT 0,
 locale_code String DEFAULT '',
 continent_code String DEFAULT '',
 continent_name String DEFAULT '',
 country_iso_code String DEFAULT '',
 country_name String DEFAULT '',
 is_in_european_union UInt8 DEFAULT 0
)
PRIMARY KEY geoname_id
SOURCE(FILE(
 path '/var/lib/clickhouse/user_files/GeoIP2-Country-Locations-en.csv'
 format 'CSVWithNames'
))
LAYOUT(HASHED())
LIFETIME(300);

Нам нужно связать ID и название страны. В заголовках GeoIP2-Country-Locations-en.csv можно найти следующие атрибуты:

• geoname_id – ID страны, как в предыдущем словаре, но теперь в качестве ключа.
• country_name — название страны.

В качестве типа размещения указываем оптимизированный hashed.

В каждом из словарей необходимо указать пути к соответствующим CSV файлам.

Теперь, имея таблицу:

CREATE TEMPORARY TABLE user_visits (user_ip String, user_id UUID);
---INSERT INTO user_visits VALUES
 ('178.248.237.68', generateUUIDv4()),
 ('82.192.95.175', generateUUIDv4());

Можем посчитать количество уникальных значений по странам. Один из способов это сделать — использовать функции для работы со словарями dictGet*:

SELECT
 dictGetString('dicts.geoip_city_locations_en', 'country_name', users_country_id) AS users_country,
 uniqs
FROM (
 SELECT
 dictGetUInt64('dicts.geoip_country_blocks_ipv4', 'geoname_id', tuple(IPv4StringToNum(user_ip))) AS users_country_id,
 uniq(user_id) AS uniqs
 FROM user_visits
 GROUP BY users_country_id
);

Ответ:

┌─users_country─┬─uniqs─┐
│ Russia │ 1 │
│ Netherlands │ 1 │
└───────────────┴───────┘
2 rows in set. Elapsed: 0.003 sec.

Разберем данный запрос:

1. Конвертируем строковое представление user_ip в числовое и оборачиваем в кортеж, чтобы соответствовать составному ключу ip_trieсловаря: tuple(IPv4StringToNum(user_ip)).
2. Используем получившийся ключ, чтобы забрать ID страны как users_country_id: dictGetUInt64('geoip_country_blocks_ipv4', 'geoname_id', ...) as users_country_id.
3. Добавляем в запрос саму метрику: uniq(user_id) as uniq_users.
4. Агрегируем по ID страны, который взяли из словаря: GROUP BY users_country_id.
5. Результат, содержащий ID стран, сопоставляем с названиями: dictGetString('geoip_city_locations_en', 'country_name', users_country_id) AS users_country.

Таким образом возможно сопоставлять не только названия стран. В тех же GeoIP2 базах есть много другой полезной информации, не бойтесь пробовать :)

Полезные ссылки:

• https://clickhouse.tech/docs/ru/sql-reference/dictionaries/external-dictionaries/external-dicts/
• https://clickhouse.tech/docs/ru/sql-reference/dictionaries/internal-dicts/
• https://habr.com/ru/company/rebrainme/blog/513972/

Задание. Правила:

1. После нажатия кнопки «Начать выполнение» для вас будет подготовлено окружение и предоставлены необходимые доступы.
2. Также вам будут выданы переменные (если они будут нужны), которые в задании указаны в фигурных скобках, — их надо будет подставить при выполнении задания.
3. После выполнения всех пунктов задания нажмите кнопку «Отправить на проверку», и в течение ближайших 3-5 минут скрипт проверит выполнение всех условий и выставит вам оценку.
4. В случае, если вы что-то забыли, можно исправить ошибки и отправить на проверку повторно.
5. Также, если вы успешно сдали задание, но у вас остались вопросы — вы всегда сможете задать их куратору после проверки или в чате в любое удобное для вас время.

Задание:

1. Создайте таблицу со структурой local_posts в базе default, которая приведена ниже.
2. Создайте локальный csv файл в /var/lib/clickhouse/user_files/users.csv, который будет содержать две колонки - user_id, email и добавьте туда две строки с данными.
3. Создайте словарь local_users_dict, который будет использовать файл, созданный в пункте 2
4. Напишите sql запрос, который будет будет преобразовывать user_id из таблицы local_posts в email пользователя из словаря.
5. Отправьте задание на проверку.

Структура таблицы:

 id Int64,
 title String,
 description String,
 content String,
 user_id Int64,
 date Date